package cn.com.callback

import cn.com.elite.EliteBean
import cn.com.elite.Util
import cn.com.kh.KHDBConfig
import cn.com.kh.KHUtil
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 * @projectName: ngsapitask-hst
 * @package: cn.com.kh
 * @className: apiCMTemplateList2
 * @author: LiJT
 * @description: TODO 页面发送WhatsApp模板消息调用(处理动态页面语言 mode=view 仅做返回 mode=deal 操作数据库)
 * @date: 2023-02-12 16:50
 * @version: 1.0
 */

public class apiCMTemplateList2 implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiCMTemplateList2.class);
    private static String log_Prefix = "页面发送WhatsApp模板消息-";

    private static String deal_Mode = "view";
    private static long startTime = System.currentTimeMillis();

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
        startTime = System.currentTimeMillis();

        JSONObject resultJson = new JSONObject();
        resultJson.put("code", 0);
        resultJson.put("msg", "");
        resultJson.put("data", []);
        resultJson.put("data_js", []);

        // 调用IP过滤
        if (KHDBConfig.getParam(eliteBean, "#APIIP") == "1" && !KHUtil.getRemoteHost(request)) {
            log.warn(log_Prefix + "API被非法IP调用-Request Not Allow!");
            resultJson.put("code", -2);
            resultJson.put("msg", "API被非法IP调用-Request Not Allow!");
            return resultJson.toString();
        }

        // 获取数据源
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());

        // 定义返回
        JSONObject resultBody = new JSONObject();

        String getMethod = request.getMethod();
        log.debug(log_Prefix + "getMethod:" + getMethod);

        //接收客户端调用参数
        String callInfo = "";
        InputStreamReader reader = new InputStreamReader(request.getInputStream(), "UTF-8");
        BufferedReader iny = new BufferedReader(reader);
        String line;
        while ((line = iny.readLine()) != null) {
            callInfo += line;
        }
        log.info(log_Prefix + "[调用请求信息]:" + callInfo);

        try {
            // 解析请求报文
            JSONObject callJson = JSONObject.fromObject(callInfo);
            String mode = callJson.getString("mode");
            deal_Mode = KHUtil.isNull(mode) ? deal_Mode : mode;
            log.info(log_Prefix + "[当前处理模式]:" + callInfo);

            if (mode.equals("view")) {
                resultBody = getSendList(eliteBean, callJson, jt);
            } else if (mode.equals("deal")) {
                resultBody = getListSend(eliteBean, callJson, jt);
            } else {
                resultBody.put("code", "-2");
                resultBody.put("msg", "不支持的调用类型mode");
            }
        } catch (Exception e) {
            log.error(log_Prefix + "接口处理异常", e);
            // 失败返回
            resultBody.put("code", "-1");
            resultBody.put("msg", "调用代理异常：" + e.getMessage());

        }

        log.debug(log_Prefix + "Proxy处理完成返回结果:" + resultBody.toString());

        return resultBody.toString();
    }

    // 通过界面调用信息返回待发送List
    public static JSONObject getSendList(EliteBean eliteBean, JSONObject callJson, JdbcTemplate jt) {

        JSONObject resultBody = new JSONObject();
        resultBody.put("code", "1");
        resultBody.put("msg", "调用成功");

        try {
            // 解析请求报文
            String rec_Guid = callJson.getString("template_guid");
            JSONArray cust_Arr = callJson.getJSONArray("cust");
            JSONArray param_Arr = callJson.getJSONArray("param");

            // 查询当前模板信息
            String templateSQL = '''Select Templates_Id, Content
                                      From Cm_Templates_Languages
                                     Where Rec_Guid = ?''';
            List<Map<String, Object>> templateList = jt.queryForList(templateSQL, rec_Guid);
            log.debug(log_Prefix + "查询当前模板信息:" + templateList.toString());
            String template_ID = templateList.get(0).get("Templates_Id").toString();
            String template_Content = templateList.get(0).get("Content").toString();
            template_Content = template_Content.replaceAll("'", "''");
            log.debug(log_Prefix + "查询当前模板内容:" + templateList);

            // 获取模板参数信息
            templateSQL = '''Select R1.Templates_Id,
                                   R1.Variable_Index,
                                   R1.Variable_Name,
                                   R1.Variable_Type,
                                   If(R2.Rec_Guid Is Null, 0, 1) As Var_Type,
                                   Concat(Case
                                            When R2.Map_Table Is Null Or R2.Map_Table = '' Then
                                             ''
                                            Else
                                             Concat(R2.Map_Table, '.')
                                          End,
                                          R2.Map_Field) As Map_Field
                              From Cm_Templates_Variable R1
                              Left Join Cm_Templates_Init_Variable R2
                                On R1.Variable_Name = R2.Variable_Id
                                 Where R1.Templates_Id = ? And R1.Del_Flag = 0
                                 Order By R1.Variable_Index''';
            templateList = jt.queryForList(templateSQL, template_ID);
            log.debug(log_Prefix + "查询当前模板参数:" + templateList.toString());

            // 解析模板数据并拼接SQL
            String ContentSQL = "";
            String ArraySQL = "";

            String Variable_Index = "";
            String Variable_Name = "";
            String Variable_Type = "";
            String Var_Type = "";
            String Map_Table = "";
            String Map_Field = "";
            for (int i = 0; i < templateList.size(); i++) {
                Variable_Index = templateList.get(i).get("Variable_Index").toString();
                Variable_Name = templateList.get(i).get("Variable_Name").toString();
                Var_Type = templateList.get(i).get("Var_Type").toString();
                Map_Field = templateList.get(i).get("Map_Field").toString();
                log.debug(log_Prefix + "当前拼接参数信息 Variable_Index:" + Variable_Index + " Variable_Name:" + Variable_Name + " Var_Type:" + Var_Type + " Map_Field:" + Map_Field);

                // 判断参数是否为预设参数 0非预设
                if (Var_Type == "0") {
                    String paramValue = "";
                    for (int j = 0; j < param_Arr.size(); j++) {
                        try {
                            JSONArray jsonArray = param_Arr.getJSONArray(j);
                            String key = jsonArray.getString(1)
                            String value = jsonArray.getString(2)

                            //处理单引号
                            value=value.replaceAll("'","\'");

                            if (key == Variable_Name) {
                                paramValue = value;
                                break;
                            }
                        } catch (Exception e) {
                            log.error(log_Prefix + "从非预设列表中查找对应值异常", e);

                        }
                    }
                    ContentSQL = KHUtil.isNull(ContentSQL) ? "Replace('" + template_Content + "','{{" + Variable_Index + "}}',If('" + paramValue + "' = '',' ','" + paramValue + "'))" : "Replace(" + ContentSQL + ",'{{" + Variable_Index + "}}',If('" + paramValue + "' = '',' ','" + paramValue + "'))";

                    ArraySQL = KHUtil.isNull(ArraySQL) ? "Concat('@#\"type\":\"text\",\"text\":\"', If('" + paramValue + "' = '',' ','" + paramValue + "'), '\"#@')" : "Concat(" + ArraySQL + ", ',' , '@#\"type\":\"text\",\"text\":\"', If('" + paramValue + "' = '',' ','" + paramValue + "'), '\"#@')";
                } else {
                    ContentSQL = KHUtil.isNull(ContentSQL) ? "Replace('" + template_Content + "','{{" + Variable_Index + "}}',If(" + Map_Field + " = '' or " + Map_Field + " is null,' '," + Map_Field + "))" : "Replace(" + ContentSQL + ",'{{" + Variable_Index + "}}',If(" + Map_Field + " = '' or " + Map_Field + " is null,' '," + Map_Field + "))";

                    ArraySQL = KHUtil.isNull(ArraySQL) ? "Concat('@#\"type\":\"text\",\"text\":\"', If(" + Map_Field + " = '' or " + Map_Field + " is null,' '," + Map_Field + "), '\"#@')" : "Concat(" + ArraySQL + ", ',' , '@#\"type\":\"text\",\"text\":\"', If(" + Map_Field + " = '' or " + Map_Field + " is null,' '," + Map_Field + "), '\"#@')";

                }

            }
            ContentSQL = KHUtil.isNull(ContentSQL) ? "Concat('" + template_Content + "')" : ContentSQL;
            ArraySQL = KHUtil.isNull(ArraySQL) ? "Concat('[', ']')" : "Concat('[', " + ArraySQL + ", ']')";

            log.debug(log_Prefix + "当前拼接SQL信息 ContentSQL:" + ContentSQL);
            log.debug(log_Prefix + "当前拼接SQL信息 ArraySQL:" + ArraySQL);

            String cust_List = "";
            for (int i = 0; i < cust_Arr.size(); i++) {
                cust_List = KHUtil.isNull(cust_List) ? "'" + cust_Arr.getJSONArray(i).getString(0) + "'" : cust_List + ", '" + cust_Arr.getJSONArray(i).getString(0) + "'";
            }

            /*String viewSQL = '''Select Distinct Customer.Customer_Guid,
                                        Customer.Mobile As Mobile,
                                        Case
                                          When Customer.Open_Id Is Not Null And Customer.Open_Id != '' Then
                                           Customer.Open_Id
                                          Else
                                           Customer.Mobile
                                        End As Whatsapp,
                                        ''' + ContentSQL + ''' As ContentSQL,
                                        ''' + ArraySQL + ''' As ArraySQL,
                                        Customer.Customer_Id,
                                        Customer.Customername,
                                        Date_Format(Objective.Createddate, '%Y-%m-%d %H:%i:%s') As Createddate,
                                        Objective.Handleby_Id
                          From Customer, Staff, c_Gender, Objective
                         Where 1 = 1
                           And Customer.Customer_Guid = Objective.Customer_Guid
                           And Objective.Handleby_Id = Staff.Staff_Id
                           And If(Customer.Gender Is Null Or Customer.Gender = '', 0, Customer.Gender) = c_Gender.Gender
                           And Objective.Map_Id = 'MY48ZX'
                           And Customer.Customer_Guid In (''' + cust_List + ''')''';*/

            String viewSQL = '''Select Distinct Customer.Customer_Guid,
                                        Customer.Mobile_enc As Mobile,
                                        Case
                                          When Customer.Open_Id Is Not Null And Customer.Open_Id != '' Then
                                           Customer.Open_Id
                                          Else
                                           Customer.Mobile
                                        End As Whatsapp,
                                        ''' + ContentSQL + ''' As ContentSQL,
                                        ''' + ArraySQL + ''' As ArraySQL,
                                        Customer.Customer_Id,
                                        Customer.Customername,
                                        Date_Format(Objective.Createddate, '%Y-%m-%d %H:%i:%s') As Createddate,
                                        Objective.Handleby_Id
                          From Customer
                          left join Objective on Customer.Customer_Guid = Objective.Customer_Guid
                          left join Staff on Objective.Handleby_Id = Staff.Staff_Id
                          left join c_Gender on If(Customer.Gender Is Null Or Customer.Gender = '', 0, Customer.Gender) = c_Gender.Gender
                         Where 1 = 1
                           And Objective.Map_Id = 'MY48ZX'
                           And Customer.Customer_Guid In (''' + cust_List + ''')
                            and Objective.handleby_id is not null
                            and Objective.handlegroup_id is not null
                            and Objective.handleby_id <> ''
                            and Objective.handlegroup_id <> '' ''';

            List call_sql_list = eliteBean.chaxun_sql(viewSQL, "dataSource");
            log.debug(log_Prefix + "代理API基础信息 Result:" + call_sql_list.toString());

            JSONArray resData = Util.getJsonArrayFromList(call_sql_list);

            resultBody.put("data", resData);

        } catch (Exception e) {
            log.error(log_Prefix + "通过界面调用信息返回待发送List 接口处理异常", e);
            // 失败返回
            resultBody.put("code", "-1");
            resultBody.put("msg", "通过界面调用信息返回待发送List异常：" + e.getMessage());

        }
        return resultBody;
    }

    // 将界面勾选的发送列表发送
    public static JSONObject getListSend(EliteBean eliteBean, JSONObject callJson, JdbcTemplate jt) {

        JSONObject resultBody = new JSONObject();
        resultBody.put("code", "1");
        resultBody.put("msg", "发送操作完成");

        try {
            // 解析请求报文
            String staff_id = callJson.getString("staff_id");
            String rolegroup_id = callJson.getString("rolegroup_id");
            String template_id = callJson.getString("template_id");
            String send_user = callJson.getString("send_user");
            String send_type = callJson.getString("send_type");
            JSONArray send_list = callJson.getJSONArray("send_list");

            //非空校验
            String dealMessage = KHUtil.isNullMSG("staff_id", staff_id) + KHUtil.isNullMSG("rolegroup_id", rolegroup_id) + KHUtil.isNullMSG("template_id", template_id) + KHUtil.isNullMSG("send_list", send_list.toString());

            if (!KHUtil.isNull(dealMessage)) {
                resultBody.put("code", "-3");
                resultBody.put("msg", "参数非空校验异常:" + dealMessage + "，调用处理时长(毫秒):" + (System.currentTimeMillis() - startTime));
            } else {

                // 查询当前模板信息
                String Customer_Guid = "";
                String Mobile = "";
                String Whatsapp = "";
                String ContentSQL = "";
                String ArraySQL = "";
                String Customer_Id = "";
                String Customername = "";
                String Handleby_Id = "";

                JSONArray sessionSendArr = new JSONArray();
                for (int i = 0; i < send_list.size(); i++) {

                    JSONObject sessionObj = new JSONObject();

                    // 获取当前行数据
                    Customer_Guid = send_list.getJSONArray(i).getString(0);
                    Mobile = send_list.getJSONArray(i).getString(1);
                    Whatsapp = send_list.getJSONArray(i).getString(2);
                    ContentSQL = send_list.getJSONArray(i).getString(3);
                    ArraySQL = send_list.getJSONArray(i).getString(4);
                    Customer_Id = send_list.getJSONArray(i).getString(5);
                    Customername = send_list.getJSONArray(i).getString(6);
                    Handleby_Id = send_list.getJSONArray(i).getString(8);

                    ArraySQL = ArraySQL.replaceAll("@#", "{").replaceAll("#@", "}");

                    log.debug(log_Prefix + "###获取当前行数据 Customer_Guid:" + Customer_Guid + " Whatsapp:" + Whatsapp + " ContentSQL:" + ContentSQL + " ArraySQL:" + ArraySQL + " Handleby_Id:" + Handleby_Id);

                    String Revoke_Id = KHUtil.getIntRandom(4) + KHUtil.getIntRandom(13);

                    String insertSQL = '''Insert Into Cm_Documentout
                                              (Documentout_Guid,
                                               Doctype,
                                               Status,
                                               Trialcount,
                                               Trialcountlimit,
                                               Template_Id,
                                               Documentpath,
                                               Documentpath2,
                                               Comments,
                                               Documentfrom,
                                               Documentto,
                                               Modifiedby,
                                               Modifieddate,
                                               CreatedGroup,
                                               Createdby,
                                               Createddate,Reference)
                                            Values
                                              (Uuid(),
                                               9,
                                               0,
                                               0,
                                               3,
                                               ?,
                                               ?,
                                               ?,
                                               '/',
                                               ?,
                                               ?,
                                               ?,
                                               Now(),
                                               ?,
                                               ?,
                                               Now(),?)''';

                    int insertNum = jt.update(insertSQL, template_id, ContentSQL, ArraySQL, send_user, Whatsapp, staff_id, rolegroup_id, staff_id, Revoke_Id);
                    log.debug(log_Prefix + "当前拼接参数信息:" + send_list.getJSONArray(i) + " DB操作数量:" + insertNum);

                    // 服顾账号&客户信息存在-检查专属客户信息表是否存在
                    String selectRouteSQL = '''Select Route_Guid
                                                  From Chat_Multichannel_Route
                                                 Where Customer_Guid = ?
                                                   And Ownerstaff_Id = ?
                                                   And Bind_Status = 1''';
                    List<Map<String, Object>> selectRouteResult = jt.queryForList(selectRouteSQL, Customer_Guid, Handleby_Id);
                    log.debug(log_Prefix + "检查专属客户信息表是否存在:" + selectRouteResult.toString());

                    if (!KHUtil.isNull(Handleby_Id) && selectRouteResult.size() == 0) {
                        // 检查专属客户信息表不存在-新增
                        String Route_Guid = KHUtil.getRandomString(36);
                        String insertRouteSQL = '''Insert Into Chat_Multichannel_Route
                                                      (Route_Guid,
                                                       Customer_Guid,
                                                       Openid,
                                                       Ownerstaff_Id,
                                                       Curvisitchannel,
                                                       Firstvisittime,
                                                       Curvisittime,
                                                       Bind_Status,
                                                       Lastupdateinfotime,
                                                       Ower_Id)
                                                      Select ?, ?, ?, ?, 'WC', Now(), Now(), 1, Now(), Ewu_Id From Staff Where Staff_Id = ?''';
                        int insertRoute = jt.update(insertRouteSQL, Route_Guid, Customer_Guid, "", Handleby_Id, Handleby_Id);
                        log.debug(log_Prefix + "新增本地对应客户WhatsApp路由信息 DB操作数量:" + insertRoute);
                    } else if (!KHUtil.isNull(Handleby_Id) && selectRouteResult.size() > 0) {
                        String updateRouteSQL = '''Update Chat_Multichannel_Route Cm, Staff St
   Set Cm.Ower_Id = St.Ewu_Id
 Where Cm.Ownerstaff_Id = St.Staff_Id
   And Cm.Customer_Guid = ?
   And Cm.Ownerstaff_Id = ?''';

                        int updateRoute = jt.update(updateRouteSQL, Customer_Guid, Handleby_Id);
                        log.debug(log_Prefix + "更新本地对应客户WhatsApp路由信息 DB操作数量:" + updateRoute);
                    }

                    // 服顾账号&客户信息存在-检查专属客户Session表是否存在
                    String selectSessionSQL = '''Select Id
                                                  From Chat_Session
                                                 Where Perm_Flag = 1
                                                   And Client_Id = ?
                                                   And Agent_Id = ?''';
                    List<Map<String, Object>> selectSessionResult = jt.queryForList(selectSessionSQL, Customer_Guid, Handleby_Id);
                    log.debug(log_Prefix + "检查[" + Handleby_Id + "]专属客户Session表是否存在:" + selectSessionResult.toString());

                    int session_ID = 0;
                    if (!KHUtil.isNull(Handleby_Id) && selectSessionResult.size() == 0) {
                        // 检查专属客户Session表是否存在-新增
                        String insertSessionSQL = '''Insert Into Chat_Session
                                                      (Start_Time, Perm_Flag, Client_Id, Agent_Id, Qa_Flag, Robot_Rating_Time, Rating_Time, Last_Queue_Id, Perm_Info)
                                                      Select Replace(Unix_Timestamp(Current_Timestamp(3)), '.', ''),
                                                             1,
                                                             ?,
                                                             Staff_Id,
                                                             '0',
                                                             '0',
                                                             '0',
                                                             15,
                                                             Concat('{\"from\":\"', Ifnull(Ewu_Id, ''),'\",\"update\":false,\"to\":\"', ?,'\",\"accountId\":\"main\",\"sessionType\":9}')
                                                        From Staff
                                                       Where Staff_Id = ?''';
                        int insertSession = jt.update(insertSessionSQL, Customer_Guid, Whatsapp, Handleby_Id);
                        log.debug(log_Prefix + "检查专属客户Session表是否存在-新增 DB操作数量:" + insertSession);

                        List<Map<String, Object>> selectSessionResult2 = jt.queryForList(selectSessionSQL, Customer_Guid, Handleby_Id);
                        session_ID = selectSessionResult2.get(0).get("Id") as int;
                        log.debug(log_Prefix + "不存在-新建后获取*专属客户Session表ID:" + session_ID);

                        String insertMsgSQL = '''Insert Into Chat_Message
                                                  (Chat_User_Id,
                                                   Chat_Session_Id,
                                                   Post_Time,
                                                   Message_Text,
                                                   Chat_Request_Id,
                                                   Content_Length,
                                                   Reply_Delay,
                                                   Message_Type,
                                                   First_Reply,
                                                   Extra,
                                                   Revoke_Flag,
                                                   Wechat_Message_Id,
                                                   Wechat_Message_Type,
                                                   Revoke_Id,
                                                   Feed_Back_Result,
                                                   Translate_Message)
                                                  Select Agent_Id,
                                                         Id,
                                                         Replace(Unix_Timestamp(Current_Timestamp(3)), '.', ''),
                                                         ?,
                                                         Null,
                                                         5,
                                                         0,
                                                         0,
                                                         0,
                                                         '',
                                                         0,
                                                         Null,
                                                         Null,
                                                         ?,
                                                         '',
                                                         ''
                                                    From Chat_Session
                                                   Where Perm_Flag = 1 And Client_Id = ?
                                                     And Agent_Id = ? And Id = ?''';
                        int insertMsg = jt.update(insertMsgSQL, ContentSQL, Revoke_Id, Customer_Guid, Handleby_Id, session_ID);
                        log.debug(log_Prefix + "检查专属客户Session表是否存在-新增 会话历史 DB操作数量:" + insertMsg);

                        sessionObj.put("sessionId", session_ID);
                        sessionObj.put("content", ContentSQL);

                        sessionSendArr.add(sessionObj);

                    } else if (!KHUtil.isNull(Handleby_Id) && selectSessionResult.size() > 0) {
                        session_ID = selectSessionResult.get(0).get("Id") as int;
                        log.debug(log_Prefix + "已存在-检查获取*专属客户Session表ID:" + session_ID);

                        String insertMsgSQL = '''Insert Into Chat_Message
                                                  (Chat_User_Id,
                                                   Chat_Session_Id,
                                                   Post_Time,
                                                   Message_Text,
                                                   Chat_Request_Id,
                                                   Content_Length,
                                                   Reply_Delay,
                                                   Message_Type,
                                                   First_Reply,
                                                   Extra,
                                                   Revoke_Flag,
                                                   Wechat_Message_Id,
                                                   Wechat_Message_Type,
                                                   Revoke_Id,
                                                   Feed_Back_Result,
                                                   Translate_Message)
                                                  Select Agent_Id,
                                                         Id,
                                                         Replace(Unix_Timestamp(Current_Timestamp(3)), '.', ''),
                                                         ?,
                                                         Null,
                                                         5,
                                                         0,
                                                         0,
                                                         0,
                                                         '',
                                                         0,
                                                         Null,
                                                         Null,
                                                         ?,
                                                         '',
                                                         ''
                                                    From Chat_Session
                                                   Where Perm_Flag = 1 And Client_Id = ?
                                                     And Agent_Id = ? And Id = ?''';
                        int insertMsg = jt.update(insertMsgSQL, ContentSQL, Revoke_Id, Customer_Guid, Handleby_Id, session_ID);
                        log.debug(log_Prefix + "检查专属客户Session表已存在-新增历史 DB操作数量:" + insertMsg);

                        sessionObj.put("sessionId", session_ID);
                        sessionObj.put("content", ContentSQL);

                        sessionSendArr.add(sessionObj);

                    } else {
                        log.warn("当前客户服顾人员ID为空,无法完成专属会话操作:" + Handleby_Id + " 检查专属客户Session表:" + selectSessionResult.size());
                    }

                }

                resultBody.put("data", sessionSendArr);

            }
        } catch (Exception e) {
            log.error(log_Prefix + "将界面勾选的发送列表发送 接口处理异常", e);
            // 失败返回
            resultBody.put("code", "-1");
            resultBody.put("msg", "将界面勾选的发送列表发送 异常：" + e.getMessage());

        }
        return resultBody;
    }

}